package org.syh.boyangfighthealthclub.dao.impl;

import org.syh.boyangfighthealthclub.dao.IUserDao;
import org.syh.boyangfighthealthclub.entity.User;
import org.syh.boyangfighthealthclub.utils.JavaDateConnection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


/**
 * @Author: Ben
 * @Date: 2021/3/8 14:55
 */
public class UserDaoImpl implements IUserDao {
    @Override
    public boolean insertUser(Connection connection, User user) {
        String sql = "insert into boxing_user (nickname,username,password,email,sex,age,role) values(?,?,?,?,?,?,?);";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getNickname());
            preparedStatement.setString(2, user.getUsername());
            preparedStatement.setString(3, user.getPassword());
            preparedStatement.setString(4, user.getEmail());
            preparedStatement.setString(5, user.getSex());
            preparedStatement.setInt(6, user.getAge());
            preparedStatement.setString(7, user.getRole());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("用户添加失败");
            e.printStackTrace();
            return false;
        }finally{
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                System.out.println("资源关闭失败");
                e.printStackTrace();
                return false;
            } finally {
               JavaDateConnection.closeCon(connection);
               return true;
            }
        }
    }

    @Override
    public boolean deleteUser(Connection connection, int id) {
        String sql = "delete from boxing_user"+" where id = ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("用户删除失败");
            e.printStackTrace();
            return false;
        }finally{
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                System.out.println("资源关闭失败");
                e.printStackTrace();
                return false;
            } finally {
                JavaDateConnection.closeCon(connection);
            }
        }
        return true;
    }

    @Override
    public boolean updateUser(Connection connection, User user) {
        String sql = "update boxing_user" + " set nickname= ?, username = ?, password = ?,  email = ?,  sex = ?, age = ?, role = ?" + " where id= ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getNickname());
            preparedStatement.setString(2, user.getUsername());
            preparedStatement.setString(3, user.getPassword());
            preparedStatement.setString(4, user.getEmail());
            preparedStatement.setString(5, user.getSex());
            preparedStatement.setInt(6, user.getAge());
            preparedStatement.setString(7, user.getRole());
            preparedStatement.setInt(8, user.getId());
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            System.out.println("用户更改失败");
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                System.out.println("资源关闭失败");
                e.printStackTrace();
                return false;
            } finally {
                JavaDateConnection.closeCon(connection);
            }
            return true;
        }
    }

    @Override
    public List<User> selectUsers(Connection connection, int pageNumber,int pageSize) {
        List<User> userList = null;
        String sql = "select * from boxing_user order by id limit ?,?";
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        try {
            userList=new ArrayList<>();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, (pageNumber - 1) * pageSize);
            preparedStatement.setInt(2, pageSize);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                userList.add(new User(resultSet.getInt("id"),
                        resultSet.getString("nickname"), resultSet.getString("username"),
                        resultSet.getString("password"), resultSet.getString("email"), resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("role")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    JavaDateConnection.closeCon(connection);
                    return userList;
                }
            }
        }
    }


    @Override
    public User selectUserByNickName(Connection connection, String nickName) {
        ResultSet resultSet = null;
        String sql = "select * from boxing_user where nickname=?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, nickName);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return new User(resultSet.getInt("id"),
                        resultSet.getString("nickname"), resultSet.getString("username"),
                        resultSet.getString("password"), resultSet.getString("email"), resultSet.getString("sex"),resultSet.getInt("age"),resultSet.getString("role"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    JavaDateConnection.closeCon(connection);
                }
            }
        }
        return null;
    }


    @Override
    public int selectCountUsers(Connection connection) {
        int count = 0;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet=null;
        String sql = "select count(*) from boxing_user";
        try {
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery(sql);
            if (resultSet.next()) {
                count = resultSet.getInt(1);
                return count;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    JavaDateConnection.closeCon(connection);
                }
            }
        }
        return count;
    }


    @Override
    public User selectUserById(Connection connection,Integer id) {
        ResultSet resultSet = null;
        String sql = "select * from boxing_user where id=?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                return new User(resultSet.getInt("id"),
                        resultSet.getString("nickname"), resultSet.getString("username"),
                        resultSet.getString("password"), resultSet.getString("email"), resultSet.getString("sex"),resultSet.getInt("age"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                try {
                    if (preparedStatement != null) {
                        preparedStatement.close();
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    JavaDateConnection.closeCon(connection);
                }
            }
        }
        return null;
    }
}
